﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;

namespace XYZShell.CsHelper.DB
{
    public class DbBase
    {
        [DisplayName("数据库地址")]
        public string Host { get; set; }
        [DisplayName("数据库名")]
        public string DbName { get; set; }
        [DisplayName("数据库用户名")]
        public string UID { get; set; }
        [DisplayName("数据库密码")]
        public string Password { get; set; }
        protected IDbConnection _dbCon;
        /// <summary>
        /// 数据库链接接口对象
        /// </summary>
        public IDbConnection DbCon
        {
            get { return _dbCon; }
            set { _dbCon = value; }
        }
        /// <summary>
        /// 获取数据库连接对象
        /// </summary>
        /// <param name="providerName">Ado驱动</param>
        /// <returns></returns>
        protected IDbConnection GetDbConnection(string providerName)
        {
            return System.Data.Common.DbProviderFactories.GetFactory(providerName).CreateConnection();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="providerName"></param>
        /// <returns></returns>
        protected IDbDataAdapter GetDbDataAdapter(string providerName)
        {
            return System.Data.Common.DbProviderFactories.GetFactory(providerName).CreateDataAdapter();
        }

    }
    public class DbHelper : DbBase, IDisposable
    {
        public DbHelper()
        {

        }
        public DbHelper(string provideName, string connectionString)
        {
            this.ProvideName = provideName;
            this.ConnectionString = connectionString;
        }
        /// <summary>
        /// Ado驱动名称
        /// </summary>
        public string ProvideName { get; set; }
        /// <summary>
        /// 链接字符串
        /// </summary>
        public string ConnectionString { get; set; }
        protected IDbTransaction _dbTrans;
        /// <summary>
        /// 打开数据库
        /// </summary>
        public void Open()
        {
            if (_dbCon == null)
            {
                _dbCon = GetDbConnection(this.ProvideName);
                _dbCon.ConnectionString = this.ConnectionString;
            }
            if (_dbCon.State == ConnectionState.Closed)
            {
                _dbCon.Open();
            }
        }
        /// <summary>
        /// 关闭数据库
        /// </summary>
        public void Close()
        {
            if (_dbCon != null && _dbCon.State == ConnectionState.Open)
            {
                _dbCon.Close();
            }
        }
        /// <summary>
        /// 开始一个事务
        /// </summary>
        public void BeginTrans()
        {
            if (_dbCon.State!= ConnectionState.Open)
            {
                Open();
            }
            if (_dbCon != null && _dbCon.State == ConnectionState.Open)
            {
                _dbTrans = _dbCon.BeginTransaction();
            }
        }
        /// <summary>
        /// 提交一个事务
        /// </summary>
        public void Commit()
        {
            if (_dbTrans != null)
            {
                _dbTrans.Commit();
                _dbCon.Close();
            }
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        public void Rollback()
        {
            if (_dbTrans != null)
            {
                _dbTrans.Rollback();
                _dbCon.Close();
            }
        }
        /// <summary>
        /// 获取一个IDbCommand
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public IDbCommand SetSql(string sql)
        {
            IDbCommand dbcmd = _dbCon.CreateCommand();
            dbcmd.CommandText = sql;
            return dbcmd;
        }
        /// <summary>
        /// 获取一个IDbCommand
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public IDbCommand SetSql(string sql, params KeyValuePair<string, object>[] parms)
        {
            IDbCommand cmd = SetSql(sql);
            foreach (KeyValuePair<string, object> item in parms)
            {
                GetAndAddParameter(cmd, item.Key, item.Value);
            }
            return cmd;
        }
        /// <summary>
        /// 获取并设置参数
        /// </summary>
        /// <param name="dbcmd"></param>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public IDbDataParameter GetAndAddParameter(IDbCommand dbcmd, string key, object value)
        {
            IDbDataParameter dparm = dbcmd.CreateParameter();
            dparm.ParameterName = key;
            if (value != null)
            {
                dparm.Value = value;
            }
            dbcmd.Parameters.Add(dparm);
            return dparm;
        }
        /// <summary>
        /// 查询一个数据列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbCmd"></param>
        /// <returns></returns>
        public List<T> Query<T>(IDbCommand dbCmd) where T : class
        {
            Type type = typeof(T);
            List<T> results = new List<T>();
            using (IDataReader reader = dbCmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    T obj = Activator.CreateInstance<T>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string fledName = reader.GetName(i);
                        object val = reader.GetValue(i);
                        System.Reflection.PropertyInfo pi = type.GetProperty(fledName);
                        pi.SetValue(obj, val, null);
                    }
                    results.Add(obj);
                }
            }
            return results;
        }
        /// <summary>
        /// 查询一个数据列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public List<T> Query<T>(string sql, params KeyValuePair<string, object>[] parms) where T : class
        {
            return Query<T>(SetSql(sql, parms));
        }
        /// <summary>
        /// 查询一行数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbCmd"></param>
        /// <returns></returns>
        public T QueryRow<T>(IDbCommand dbCmd) where T : class
        {
            Type type = typeof(T);
            using (IDataReader reader = dbCmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    T obj = Activator.CreateInstance<T>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string fledName = reader.GetName(i);
                        object val = reader.GetValue(i);
                        System.Reflection.PropertyInfo pi = type.GetProperty(fledName);
                        pi.SetValue(obj, val, null);
                    }
                    return obj;
                }
            }
            return default(T);
        }
        /// <summary>
        /// 查询一行数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public T QueryRow<T>(string sql,params KeyValuePair<string,object>[] parms) where T : class
        {
            return QueryRow<T>(SetSql(sql, parms));
        }
        /// <summary>
        /// 获取表的第一行第一列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbCmd"></param>
        /// <returns></returns>
        public T ExecuteScalar<T>(IDbCommand dbCmd)
        {
            return (T)dbCmd.ExecuteScalar();
        }
        /// <summary>
        /// 获取表的第一行第一列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public T ExecuteScalar<T>(string sql, params KeyValuePair<string, object>[] parms)
        {
            return ExecuteScalar<T>(SetSql(sql, parms));
        }
        /// <summary>
        /// 查询一列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbCmd"></param>
        /// <returns></returns>
        public List<T> QueryCols<T>(IDbCommand dbCmd)
        {
            List<T> results = new List<T>();
            using (IDataReader reader = dbCmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string fledName = reader.GetName(i);
                        object val = reader.GetValue(i);
                        results.Add((T)val);
                        break;
                    }
                }
            }
            return results;
        }
        /// <summary>
        ///  查询一列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public List<T> QueryCols<T>(string sql, params KeyValuePair<string, object>[] parms)
        {
            return QueryCols<T>(SetSql(sql, parms));
        }
        /// <summary>
        /// 获取数据源
        /// </summary>
        /// <param name="dbCmd"></param>
        /// <returns></returns>
        public DataSet GetDataSet(IDbCommand dbCmd)
        {
            IDbDataAdapter dbdap = GetDbDataAdapter(ProvideName);
            dbdap.SelectCommand = dbCmd;
            DataSet ds = new DataSet();
            dbdap.Fill(ds);
            return ds;
        }
        /// <summary>
        ///  获取数据源
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql, params KeyValuePair<string, object>[] parms)
        {           
            return GetDataSet(SetSql(sql, parms));
        }
        /// <summary>
        /// 获取表
        /// </summary>
        /// <param name="dbCmd"></param>
        /// <returns></returns>
        public DataTable GetTable(IDbCommand dbCmd)
        {
            DataSet ds = GetDataSet(dbCmd);
            return ds.Tables[0];
        }
        /// <summary>
        /// 获取表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public DataTable GetTable(string sql, params KeyValuePair<string, object>[] parms)
        {
            return GetTable(SetSql(sql,parms));
        }
        /// <summary>
        /// 执行sql返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, params KeyValuePair<string, object>[] parms)
        {
            IDbCommand cmd = SetSql(sql);
            if (parms!=null)
            {
                foreach (KeyValuePair<string,object> item in parms)
                {
                    GetAndAddParameter(cmd, item.Key, item.Value);
                }
            }            
            return cmd.ExecuteNonQuery();
        }
        
        public void Dispose()
        {
            this.Close();
        }
    }
}
